-- This files takes off where 02_certsigndb.sql ended.
SET NOCOUNT ON
USE PermTest
go

-- Let's first look at the token information directly.
SELECT SYSTEM_USER AS [SYSTEM_USER], USER AS DBUSER, 
       name AS token_name, type, usage 
FROM   sys.user_token
go
-- We first create an inner procedure.
CREATE PROCEDURE inner_sp AS
   -- Return token information.
   SELECT 'inner_sp' AS SP, SYSTEM_USER AS [SYSTEM_USER], USER AS DBUSER,
          name, type, usage 
   FROM   sys.user_token

   -- Attempt to retrieve meta-data.
   SELECT object_id('PermTable') AS PermTable_objid
go  
-- Add a trigger on the PermTest table.
CREATE TRIGGER PermTable_tri ON PermTable FOR INSERT AS
  SELECT 'PermTable_tri', name, type, usage FROM sys.user_token
go

-- Next, we create an outer procedure.
CREATE PROCEDURE outer_sp @a int, @b varchar(30) AS
   -- Return token information.
   SELECT 'outer_sp' AS SP, SYSTEM_USER AS [SYSTEM_USER], USER AS DBUSER,
          name AS token_name, type, usage 
   FROM   sys.user_token

   -- Run some dynamic SQL.
   EXEC ('SELECT ''Dynamic SQL'' AS SP, name AS token_name, type, usage 
          FROM   sys.user_token
          SELECT a, b, enteredby FROM PermTable')

   -- Run a system procedure.
   EXEC sp_helpindex PermTable

   -- Run the inner procedure.
   EXEC inner_sp

   -- Add a row to the table and fire the trigger.
   INSERT PermTable(a, b) VALUES(@a, @b)

   -- Attempt to truncate the table.
   PRINT 'Attempt to truncate.'
   TRUNCATE TABLE PermTable
go

-- The test user needs permission to run the outer procedure. (But not the inner -
-- ownership chaining!)
GRANT EXECUTE ON outer_sp TO TestUser
go

-- Run as the test user. Plenty of errors.
EXECUTE AS USER = 'TestUser'
go
SELECT SYSTEM_USER AS [SYSTEM_USER], USER AS DBUSER, 
       name AS token_name, type, usage 
FROM   sys.user_token 
EXEC outer_sp 10, 'Helter Skelter'
go
REVERT

-- Now sign the outer procedure. (Not the inner!)
ADD SIGNATURE TO outer_sp BY CERTIFICATE DemoCert 
    WITH PASSWORD = 'All You Need is Love'
go

-- Run as the test user again. 
EXECUTE AS USER = 'TestUser'
go
SELECT SYSTEM_USER AS [SYSTEM_USER], USER AS DBUSER, 
       name AS token_name, type, usage 
FROM   sys.user_token 
EXEC outer_sp 11, 'Being for the Benefit of Mr Kite'  
go
REVERT

-- One more thing: Our testuser has been a naughty boy why will we explicitly deny
-- him access to the PermTable.
DENY SELECT, INSERT, UPDATE, DELETE ON PermTable TO TestUser
go
-- Now we trying running the first test procedure again
EXECUTE AS USER = 'TestUser'
go
-- Dynamic SQL and sp_helpindex fails, but INSERT succeds.
EXEC outer_sp 12, 'The Long and Winding Road'
go
REVERT
go

-- Remove the DENY - it is best avoided.
REVOKE SELECT, INSERT, UPDATE, DELETE ON PermTable TO TestUser
go

-- Drop the trigger on PermTable for now.
DROP TRIGGER PermTable_tri
